X by Y Sales Commission Report (Regular Format) - COM 5

Support for Negative Commission Write-offs

Commissions based on Parent or Child items

The X by Y Sales Commission Report supports all of the sales commissions strategies available within the system. This report lists, in detail or summary format, the sales, gross profit, and commission calculations, sorted using any X by Y combination.

When running this report, the system prompts you for the basis of the commission: invoices created or invoices paid. The system also prompts you to include commission exceptions for aged invoices, and many other options.
Enter an R to run the report for range of dates and press Enter.

Decide if you want the commission report to be for invoices or payments, enter the appropriate option and press Enter. The decision to run the report for invoices or payments affects the information contained in the X by Y Sales Commission Report, Options Screen.
Next, select an option for salesperson assignments.

Option 1, the default option, uses the salespersons assigned on each invoice.
Option 2 uses the salespersons currently assigned to each customer account, if any. This option may be effective when you are reassigning salespeople and territories. It provides a way to show what a newly assigned salesperson or territory would have earned in any historical period on the system.
Option 3 is the same as option 1, except that whenever a secondary salesperson is found on an invoice, that invoice will be split between the two salespeople using the percentage keyed on the above screen. For example, a $100 invoice with a 25% split will appear as $75 for the primary salesperson, and $25 for the secondary salesperson.

Enter an option and press Enter. The next screen lets you pick a date range, and other parameters which can limit the amount of data retrieved for the report.

The fields at the bottom of the screen (described in the table below) control how gross profit percentages will be calculated. The default values are the recommended values for each field.

Field	Description
Include Trim	Enter N to omit sales of items that have a trim class in the Item File.
Use Invoice, Standard, or House Costs	Enter IC to use the actual cost on each invoice/line. Enter SC to use the Standard Costs from the Cost or Item Files instead of the invoice costs. Enter HC to use the House Cost from the Cost File instead if the invoice costs. GP% is calculated using the cost selected here. The default is IC.
Include Sales Allowances	Enter Y to include the sales allowance (promo) portion of the price. Enter N to remove the sales allowance from the price. Y is the default.
Include Funds	Enter Y to include the fund/overbill portion of the price. Normally you should not pay commissions on funds. N is the default.
Include Cost Allowances	Enter Y to include the cost allowance (rebate) portion of the cost. Y is the default.
Include Only Sales Within GP% Span	You may limit the report to invoice/lines within an specified gross profit percentage range.

Enter an invoice number range and/or date range and press Enter.

The next series of screens let you pick the X and Y parameters. They are common to all X by Y reports. Normally for sales commissions your X parameter is Salesperson#, and your Y parameter is Cust Account#.

The next screen includes options that are specific to the X by Y commissions. The information contained in the following screen varies depending on whether you are running the report for invoices or payments. The figure and text below describe the screen that appears if the invoice option is taken.

Choose if you want to run the report for paid invoices (P), unpaid invoices (U), or both (B). When running by invoice dates, the default option is B, to include all invoices regardless of payment.
Enter the days past due date you want to consider for the report, if you select U for the previous option. This enables you to run the report as a collection tool, listing only past due invoices for the salesperson, to help with collections.
Enter a Y in the Do you want to include Commission Exceptions field. The system will use the settings made in the Commission Exception File to calculate and report commissions.
The second part of the screen is devoted to options if a secondary salesperson is involved in the sale. Enter a Y in the Do you want to access the Secondary Salespeople field, if you want to consider the secondary salespeople.

Secondary salespeople are added on the second line in the Salesperson field during order entry or through the Maintain Salesperson Assignments program.

Enter either a P, S, or B in the next field to print the commissions for the primary salesperson (P), secondary salesperson (S) or for both the primary and the secondary (B).
If you choose to run both reports, one for the primary and one for the secondary, the reports will run one after another. To avoid confusion, the primary and secondary information is not included on the same report. This allows for different formulas to apply to primary versus secondary reports.
Select one of the four secondary salesperson commission calculation options.

Use___ .___ % Commission, on (S/P) - Enter a commission percentage to be paid to the secondary salesperson. You can specify whether you want the commission percentage to come from the sale amount or the amount of gross profit generated from the sale, by entering an S or P.
Use ___.___ % of the Primary Slmn's Commission - Enter a percentage here, if you want to calculate the secondary salespersons' commission as a percentage of the primary salespersons' commission dollars. Using this option in conjunction with the option Reduce Primary Slmn's Commission by the Secondary Slmn's Comm creates a split commission. For example, if you entered 10.00 in this field and flagged the reduce option as Y, ten percent of the primary salesperson's commission will go to the secondary salesperson.
Use Commission% from the Salesperson Master File - Normally the commission percentage in the Salesperson Master File is not used within the X by Y Commissions Program. The exception to this is that the secondary salesperson may use this method. This may be helpful when the secondary salespeople may also be primary salespeople, depending upon the sale. In this case, their commissions as primary salespeople are determined by the Base Commissions and Commissions Exceptions File, while their commissions as secondary salespeople are determined by the Salesperson File.
Use Commission% from the Base & Exception Commission Files - Uses the Base and Exception Commission Table for the secondary salesperson. The commission for the secondary salesperson is calculated independently from the primary, using the same files. A primary salesperson's commission is always based on the Base Commission and Exceptions tables.

Reduce Primary Slmn's Commission by the Secondary Slmn's Comm causes whatever the commission is for the secondary salesperson to be subtracted from the primary salespersons' commission dollars.
Enter a Y in the Use Commission Override% if found on specific orders/invoices field if you want to access the commissions for secondary salespeople that are overridden using the Maintain Salesperson Assignment program. Whenever an override is found it will be used.

Note: Your entries on this screen are saved and displayed as defaults the next time you run this report.

Payment Option

This screen appears when commissions are paid based upon payments received, such as commissions for invoices paid in Oct., 2003. The primary difference between the invoice and the payment options are three fields at the top of the screen.

Enter a Y in the field Do you want to use the Commission Table Based Upon Aging to take advantage of the settings made in the Commission Aging Parameters screen of the Base Commissions File.
If you want to include commissions on partial payments, enter a Y in the field Do you want to include Commissions on Partial Payments.

If an invoice is partially paid within the specified payment date range, and you enter N, the system does not include the invoice when calculating commission. The program only pays commission on the full invoice amount when it is paid in full, or when a final partial payment is made which closes out the invoice.
If you enter Y the system divides the partial payment amount by the invoice amount to get a percentage of invoice dollars paid. This percentage is used to adjust the commission dollars. The salesperson is only paid commission on the percentage of the invoice that was paid.

The system handles partial payments as follows.

Let's start with an invoice for $100.00 where the salesperson gets a 5% commission on a gross profit amount of $20.00. Let's assume that the aging parameters cause the commission to be reduced by 2% if the invoice is paid between 31 and 45 days from the due date, and reduced by 3% if paid between 46 and 60 days from the due date. For this example, let's assume the payment was made in 35 days, leaving a 3.00% commission. A partial payment of $75.00 is made on this invoice. The X by Y Commission report would see the partial pay invoice and calculate the partial payment factor as 75/100, which equals 0.75. The system, then multiplies the 3.00% by the 0.75 partial payment factor, which equals 2.25%. The 2.25% is used in the commissions report, as the applicable commission rate. The report shows the partial payment factor. This is the equivalent of paying commission only on the portion of the invoice that was paid.

If an invoice is partially paid in one month and the remaining balance is paid in full the following month or later, then the X by Y Commission Report only picks up the amounts of each partial payment and calculates the commission in the month when each payment is made. Using the example above, if the remaining balance on the invoice of $25.00 was paid in 45 - 60 days, and the 5.00% commission is reduced by 3.00%, then the commission report uses the following formula: The partial payment is .25 of the original invoice or 25/100. The remaining 2.00% commission is multiplied by the partial payment factor of .25, which equals a 0.50% commission rate.

If the invoice is partially paid one month, and the next month it is partially paid again, then the X by Y Commission report uses each partial payment. The calculation is similar to the one in the previous paragraph. It uses the amount paid in the payment date range specified. Regardless of the number of payments made, the sum of the partial payments can never exceed the total invoice amount.

The complete formula is as follows: Commission % = (Paid Amount/Invoice Amount) x (Original Base and Exceptions Commission % minus Aging Adjustment %).

With either partial payment option, N or Y, the same amount of commission is paid on any invoices that are paid in full. If you enter N, commission is only paid once, when the final payment is received. If you enter Y, commissions are paid on each partial payment. If you enter N, invoices that are never paid in full earn no commissions.

Note: The following payment codes, used in accounts receivable to clear an invoice, are NOT included in the X by Y Commission reports on paid invoices: WW, OA, AD, DM, WC, WN, and WP. These codes are used for write-offs and adjustments, which are not considered to be payments. Discounts allowed at the time of payment are considered to be part of the payment. Therefore, salespeople are paid for the full invoice amount even when an early pay discount is allowed.

The Increase Cost of Sales field allows you to add an overhead cost which reduces gross profit and commissions if commissions are based on gross profits. For example, if you enter 3.75%, then the costs on all invoices will be increased by 3.75% on the X by Y Commissions Report.
The next X by Y Sales Commission screen is the confirmation screen.

This screen confirms your X by Y parameters. You can enter X to start a new page for each salesperson (assuming your X parameter is salesperson). You may specify Y for Summary Report, and only the X by Y subtotals will print. Enter N for the regular detailed version, showing each line item sold. The customer version drops out cost and profit information. The default is M for management version, unless the user is coded to not see costs.
The final screen schedules the job. Enter a 1 to run the report.

Support for Negative Commission Write-offs

The commission reports can produce an additional report showing negative commission write-offs based AR write-off code WZ. This report can be used to impact salesperson commission amounts which are generated by write-offs in AR for amounts that were never paid.

This functionality has to be activated via the System Wide Setting - Options for Salesperson Commissions.

Use the AR Ledger to find each account's current balances as well as aging and all open transactions, or those paid or closed in the current month.

An Example

Let's look at the reports for just one invoice that had write-offs on a couple of lines.

The commission report, which shows how much commission was paid per invoice, shows that $52.11 was paid in commissions for invoice 411691 (the report was run just for that invoice).

When we look at the commission write-off report, we see that $14.21 in commissions has been written off and should be taken off the $52.11 commission that the salesperson was originally slated to receive.

Here is how the commission write-offs were calculated.

By looking at the AR Ledger for Invoice 411691 we can see a $250 write-off.
To find the percentage of the invoice that was not paid divide 250/579. The $579 is the total Extended Price of the invoice. It is not the invoice total since it does not include any fees, charges or taxes.

250/579 = 43.17%

So now we know that 43.17 percent of the invoice was not paid for. The system uses that percentÂage to calculate how much should be deducted from the original commissions. The first and third lines had negative commissions (-17.00 and - 3.00)

Original Reduction

Commission Amount

39.37 x .4317 17.00

6.95 x .4317 3.00

Add all the commission payments, both the negative and the positive, and the total is 14.21. 52.11 - 14.21 = 37.90 (new commission payment)

Commissions based on Parent or Child items

This functionality pertains to Crossover items. Crossover items are two or more item numbers assigned to the same inventory item. The two items have the same inventory, but can have different item numbers and different descriptions. You can use crossover items to market the same product under multiple numbers and names. For example, two different customers can have exclusive sales rights to the same item. Both customers can place orders under their own assigned item numbers and descriptions, and receive all documents (pick lists, invoices, bar code labels) with their assigned item numbers and descriptions, even though the actual inventory is the same.

The item containing the inventory is known as the parent item. The dependent items are known as child items.

This functionality allows you to report sales commissions based on the salesperson assigned to the parent item or to the child item.

The System Wide Setting - Salesperson Commission Options lets you decide to base commissions on parent or child items.

Associated Files

Assigning Salespeople to Orders

The Kerridge system supports several strategies for assigning salespeople to orders. These strategies can be used concurrently, and in some cases used in combination on the same order. Salesperson Codes can be assigned to orders in the following places:

Header of an order - Via the Salesperson field
Billto File - Account profile screen. This is the default unless overridden.
F14 Codes screen of the Billto File

Assigning Salespeople to Invoices

Salesperson Codes can be assigned to invoices in the following places:

The salesperson assignments on an order automatically flow through to all invoices for that order, unless overridden on the invoice.
The salesperson assignment for an invoice may be overridden by keying a salesperson code on the header of the invoice when the order is invoiced.
The salesperson assignment may be overridden even after invoicing, by using the Maintain SalesÂperson Assignments by Order or Invoice option on the Commissions menu.

Item File

For child items ensure the parent item number is entered into the Xref field.

An Example

To show how this functionality works consider the following two items.

REX1148 is the parent item (no entry in the Xref field). Also notice that this item is in Item Class (1) F1.

The item class is important because it is one way that salespeople can be assigned to items. This can be checked by accessing the Billto File for the account and then pressing F14 on the profile screen to display the Additional Codes screen. Note in the screen below, salesperson JHJ is assigned to Item Class F1 items.

REX1150 is set up as a child item to REX1148. It is an Item Class 1 FT item which we can in the screen above item class FT is assigned to salesperson MDH.

Now all that is left to do is run the report.

For the purposes of this example, the report is run against a very narrow data set.

The report can be produced as a spool file, a pdf, or as a spreadsheet. A spreadsheet example is shown below.

Associated Files

Maintain X By Y Job Queues - SET 305